﻿/// <summary>
///Author: Jignesh Chaudhari
///Written: 2013-Jan 18
///Purpose: Database functionality-main purpose data seperation
/// </summary>
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.IO;
using System.Data;

namespace TravelExpertsASP
{
    class DbAccess
    {
        //encapsulated variables
        private SqlConnection _myConn;
        private SqlCommand _myComand;

        //constructor
        public DbAccess()
        {
            //initializing connection and command variables

            _myConn = new SqlConnection("server=(local);database=TravelExperts;integrated security=SSPI");
            _myComand = new SqlCommand();
            _myComand.Connection = _myConn;
        }

        /// <summary>
        /// Author: Jignesh Chaudhari
        /// Authenticating user for login functionality
        /// </summary>
        /// <param name="uname">user name is email address</param>
        /// <param name="password">password text field</param>
        /// <returns>Return boolean value</returns>
        public bool AuthenticateUser(string uname, string password)
        {
            bool bflag = false;


            string strSQL = "Select * from Customers where CustEmail='" + uname + "' And CustPWord='" + password + "'";
            DataSet userDS = new DataSet();
            
            SqlDataAdapter m_dataAdapter;
           
            try
            {
                
                _myConn.Open();
                m_dataAdapter = new SqlDataAdapter(strSQL, _myConn);
                m_dataAdapter.Fill(userDS);
                _myConn.Close();
            }
            catch (Exception)
            {   
                userDS = null;
            }

            if (userDS != null)
            {
                if (userDS.Tables[0].Rows.Count > 0)
                    bflag = true;
            }
            return bflag;
        }


        //retriving list of Packages objects from the database in a list collection


        public List<Packages> SelectPackages()
        {
            List<Packages> myPackages = new List<Packages>();

            //set the command text
            _myComand.CommandText = "Select TOP 3 * from Packages Order by PackageId DESC";
            try
            {
                _myConn.Open();
                SqlDataReader pkgReader = _myComand.ExecuteReader();

                while (pkgReader.Read())
                {
                    //initializing products object

                    Packages objPackages = new Packages();

                    objPackages.PackageId = pkgReader.GetInt32(0);
                    objPackages.PkgName = pkgReader.GetString(1);
                    DateTime stDt = pkgReader.GetDateTime(2);
                    DateTime endDt = pkgReader.GetDateTime(3);
                   string strDt = stDt.ToString("dd/MM/yyyy");
                   string enDt = endDt.ToString("dd/MM/yyyy");
                  // DateTime startedDate = DateTime.ParseExact(strDt, "d", null);
                  // DateTime myDate = DateTime.ParseExact(strDt, "d", System.Globalization.CultureInfo.InvariantCulture);

                   objPackages.PkgStartDate = strDt; //pkgReader.GetDateTime(2);
                    objPackages.PkgEndDate = enDt;
                    
                    objPackages.PkgDesc = pkgReader.GetString(4);
                    objPackages.PkgBasePrice = pkgReader.GetDecimal(5);
                    objPackages.PkgBasePrice = Math.Round(objPackages.PkgBasePrice, 2);
                    
                    objPackages.PkgAgencyCommission = pkgReader.GetDecimal(6);
                    objPackages.PkgAgencyCommission = Math.Round(objPackages.PkgAgencyCommission, 2);
                    
                    myPackages.Add(objPackages);

                }
                pkgReader.Close();
            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                _myConn.Close();
            }


            return myPackages;
        }
        ////////////update the customer information///////////////////////////
        ////////////Added by Qingshenrg Zhou(Jason)////////////////////////
        public void UpdateCustomers(Customer myCust)
        {
            _myComand.CommandText = "UPDATE Customers SET CustFirstName = @CustFirstName,CustLastName = @CustLastName " +
                ",CustAddress = @CustAddress, CustCity = @CustCity, CustProv = @CustProv, CustCountry = @CustCountry " +
                " WHERE CustEmail=@CustEmail";
            ExeCustomers(myCust);
        }
        private void ExeCustomers(Customer myCust)
        {
            //_myComand.Parameters.AddWithValue("@AgentId", myCust.AgentId);
            _myComand.Parameters.AddWithValue("@CustFirstName", myCust.CustFirstName);
            _myComand.Parameters.AddWithValue("@CustLastName", myCust.CustLastName);
            _myComand.Parameters.AddWithValue("@CustAddress", myCust.CustAddress);
            _myComand.Parameters.AddWithValue("@CustCity", myCust.CustCity);
            _myComand.Parameters.AddWithValue("@CustProv", myCust.CustProv);
            //_myComand.Parameters.AddWithValue("@CustPostal", myCust.CustPostal);
            _myComand.Parameters.AddWithValue("@CustCountry", myCust.CustCountry);
            //_myComand.Parameters.AddWithValue("@CustHomePhone", myCust.CustHomePhone);
            //_myComand.Parameters.AddWithValue("@CustBusPhone", myCust.CustBusPhone);
            _myComand.Parameters.AddWithValue("@CustEmail", myCust.CustEmail);
            //_myComand.Parameters.AddWithValue("@CustPWord", myCust.CustPWord);


            try
            {
                _myConn.Open();
                _myComand.ExecuteNonQuery();
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                _myConn.Close();
            }
        }

        ////////////get the customer information///////////////////////////
        ////////////Added by Qingshenrg Zhou(Jason)////////////////////////
        //public List<Customer> custInfo(string emailAddr)
        public Customer custInfo(string emailAddr)
        {
          //  emailAddr = "hlopez@aol.com";
            //string CustEmail = emailAddr;
            //List<Customer> myCustomer = new List<Customer>();
            Customer myCustomer = new Customer();
            _myComand.CommandText = "SELECT * from Customers WHERE CustEmail = @CustEmail2";
            _myComand.Parameters.AddWithValue("@CustEmail2", emailAddr);
          
            try
            {
                _myConn.Open();

                SqlDataReader infoReader = _myComand.ExecuteReader();

                while (infoReader.Read())
                { 
                    //Customer myCust = new Customer();

                    myCustomer.CustFirstName = infoReader.GetString(1);
                    myCustomer.CustLastName = infoReader.GetString(2);
                    myCustomer.CustAddress = infoReader.GetString(3);
                    myCustomer.CustCity = infoReader.GetString(4);
                    myCustomer.CustProv = infoReader.GetString(5);
                    myCustomer.CustCountry = infoReader.GetString(7);

                    //myCustomer.Add(myCust);
                }

                infoReader.Close();
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                _myConn.Close();
            }
            return myCustomer;
        }

        ////////////Get the past order information///////////////////////////
        ////////////Added by Qingshenrg Zhou(Jason)////////////////////////
        public SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();
        public DataTable selectCustFromEmail(String emailAddr)
        {
            //emailAddr = "hlopez@aol.com";
            DataTable myView = new DataTable();
            //emailAddr= Session["custEmail"];
            string myStr = "SELECT Customers.CustFirstName, Products.ProdName, Bookings.BookingDate, BookingDetails.BasePrice " +
            "FROM  BookingDetails INNER JOIN " +
            "Bookings ON BookingDetails.BookingId = Bookings.BookingId INNER JOIN " +
            "Customers ON Bookings.CustomerId = Customers.CustomerId INNER JOIN " +
            "Products_Suppliers ON BookingDetails.ProductSupplierId = Products_Suppliers.ProductSupplierId INNER JOIN " +
            "Products ON Products_Suppliers.ProductId = Products.ProductId " +
            "where CustEmail = " +"'"+ emailAddr+"'";
            try
            {
                _myConn.Open();
                SqlCommand myCommand = new SqlCommand(myStr, _myConn);
                mySqlDataAdapter.SelectCommand = myCommand;
                mySqlDataAdapter.Fill(myView);
                //myTable.DataSource = myView;
                //myTable.DataBind();

                decimal result = 0;

                ////////////////get the total price of one customer///////////////

                for (int i = 0; i < myView.Rows.Count; i++)
                {
                    result += Convert.ToDecimal(myView.Rows[i][3]);
                }

                //lblPrice.Text = "Total Price : " + Convert.ToString(result);
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                _myConn.Close();
            }

            return myView;
        
        }

        public List<Customer> SelectCustomer()
        {
            List<Customer> myCustomer = new List<Customer>();

            //Query Command Text
            _myComand.CommandText = "Select * from Customers";

            try
            {
                _myConn.Open();
                SqlDataReader custReader = _myComand.ExecuteReader();

                while (custReader.Read())
                {
                    //initializing new instance of Customer

                    Customer objCustomer = new Customer();

                    objCustomer.AgentId = custReader.GetInt32(0);
                    objCustomer.CustFirstName = custReader.GetString(1);
                    objCustomer.CustLastName = custReader.GetString(2);
                    objCustomer.CustAddress = custReader.GetString(3);
                    objCustomer.CustCity = custReader.GetString(4);
                    objCustomer.CustProv = custReader.GetString(5);
                    objCustomer.CustPostal = custReader.GetString(6);
                    objCustomer.CustCountry = custReader.GetString(7);
                    objCustomer.CustHomePhone = custReader.GetString(8);
                    objCustomer.CustBusPhone = custReader.GetString(9);
                    objCustomer.CustEmail = custReader.GetString(10);
                    
                    myCustomer.Add(objCustomer);

                }
                custReader.Close();
            }
            catch (Exception)
            {
               throw;
            }
            finally
            {
                _myConn.Close();
            }


            return myCustomer;
        }


        //Inserting to the customer Table in the DB

        public void InsertCustomer(Customer myCust)
        {
            _myComand.CommandText = "INSERT INTO Customers VALUES (@CustFirstName,@CustLastName,@CustAddress,@CustCity,@CustProv,@CustPostal,@CustCountry,@CustHomePhone,@CustBusPhone,@CustEmail,@AgentId,@CustPWord)";
            ExecuteNonReadCustomers(myCust);
        }

        public void UpdatePackages(Customer myCust)
        {
            _myComand.CommandText = "UPDATE Packages SET CustFirstName=@CustFirstName,CustLastName=@CustLastName" +
                "CustAddress=@CustAddress,CustCity=@CustCity,CustPostal=@CustPostal,CustCountry=@CustCountry" +
                "CustHomePhone=@CustHomePhone,CustBusPhone=@CustBusPhone,CustEmail=@CustEmail,AgentId=@AgentId,CustPWord=@CustPWord" +
                "WHERE CustomerId=@CustomerId";
            ExecuteNonReadCustomers(myCust);
        }


        //Don't really need a delete method as I don't think the customer is allowed to delet their own account.
              
        private void ExecuteNonReadCustomers(Customer myCust)
        {
            _myComand.Parameters.AddWithValue("@AgentId", myCust.AgentId);
            _myComand.Parameters.AddWithValue("@CustFirstName", myCust.CustFirstName);
            _myComand.Parameters.AddWithValue("@CustLastName", myCust.CustLastName);
            _myComand.Parameters.AddWithValue("@CustAddress", myCust.CustAddress);
            _myComand.Parameters.AddWithValue("@CustCity", myCust.CustCity);
            _myComand.Parameters.AddWithValue("@CustProv", myCust.CustProv);
            _myComand.Parameters.AddWithValue("@CustPostal", myCust.CustPostal);
            _myComand.Parameters.AddWithValue("@CustCountry", myCust.CustCountry);
            _myComand.Parameters.AddWithValue("@CustHomePhone", myCust.CustHomePhone);
            _myComand.Parameters.AddWithValue("@CustBusPhone", myCust.CustBusPhone);
            _myComand.Parameters.AddWithValue("@CustEmail", myCust.CustEmail);
            _myComand.Parameters.AddWithValue("@CustPWord", myCust.CustPWord);

            
            try
            {
                _myConn.Open();
                _myComand.ExecuteNonQuery();
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                _myConn.Close();
            }
        }
    }
}

